package it.unibz.easytravel.db;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import it.unibz.easytravel.common.Person;
import it.unibz.easytravel.travel.EditTravel;
import it.unibz.easytravel.travel.Travel;
import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class TravelDbAdapter {

	private static final String TAG = "TravelDb";

	private static final String TABLE_NAME = "travel";
	public static final String TRAVEL_ID = "_id";
	public static final String TITLE = "title";
	public static final String DESCRIPTION = "description";
	public static final String CREATOR_ID = "creator_id";

	private DatabaseHelper mDbHelper;

	private SQLiteDatabase mDb;

	private final Context mCtx;

	/**
	 * Database creation sql statement
	 */
	private static final String DATABASE_CREATE = 
		"create table " + TABLE_NAME + " ("
		+ TRAVEL_ID + " integer primary key autoincrement, "
		+ TITLE + " text, "
		+ DESCRIPTION + " text,"
		+ CREATOR_ID + " integer);";



	public static void onCreate(SQLiteDatabase db) {
		db.execSQL(DATABASE_CREATE);
	}

	public static void onUpgrade(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		onCreate(db);
	}

	public TravelDbAdapter(Context ctx) {
		this.mCtx = ctx;
	}

	public TravelDbAdapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	public void delete() {
		Log.i(TAG, "deleteTable");
		mDb.execSQL("DELETE FROM " + TABLE_NAME);
	}

	public void delete(int travelId, Activity activity) {
		Log.i(TAG, "deleteTable(travel_id)");
		mDb.execSQL("DELETE FROM " + TABLE_NAME+" WHERE "+ TRAVEL_ID +"="+travelId);
		
		TravellersDbAdapter mTravellersAdapter = new TravellersDbAdapter(activity);
		mTravellersAdapter.open();
		
		TravellersDbAdapter.removeAllTravellers(travelId);
		
		mTravellersAdapter.close();
	}

	/**
	 * Insert a new travel without creating the Travel object 
	 */
	public long insertTravel(String name, String description, int creator_id) {
		Log.i(TAG, "insertTravel");
		ContentValues values = new ContentValues();
		values.put(TITLE, name);
		values.put(DESCRIPTION, description);
		values.put(CREATOR_ID, creator_id);
		return mDb.insert(TABLE_NAME, null, values);
	}


	/**
	 * Insert a new travel using the Travel object 
	 */
	public long insertTravel(Travel travel, Activity activity) {
		Log.i(TAG, "insertTravel");

		PersonDbAdapter mPersonDbAdapter = new PersonDbAdapter(activity);
		TravellersDbAdapter mTravellersAdapter = new TravellersDbAdapter(activity);
		mTravellersAdapter.open();
		mPersonDbAdapter.open();

		long creator_id = PersonDbAdapter.insertPerson(travel.getCreator().getName(),travel.getCreator().getPhoneNumber());

		ContentValues values = new ContentValues();
		values.put(TITLE, travel.getTitle());
		values.put(DESCRIPTION, travel.getDescription());
		values.put(CREATOR_ID, creator_id);

		long travel_id = mDb.insert(TABLE_NAME, null, values);

		TravellersDbAdapter.insertTraveller(travel_id, creator_id);

		// loop through travellers, check if the person is in db, if yes, get _id,
		// if no, insert the person and get the _id;
		// insert each person_id together with travel_id into Travellers table

		Iterator<Person> iTravellers = travel.getTravellers().iterator();
		while (iTravellers.hasNext()){
			Person traveller = (Person)iTravellers.next();
			long person_id = -1;

			if (PersonDbAdapter.personExists(traveller.getPhoneNumber())){
				person_id = PersonDbAdapter.getPersonId(traveller.getPhoneNumber());
			} else {
				person_id = PersonDbAdapter.insertPerson(traveller.getName(),traveller.getPhoneNumber());
			}

			TravellersDbAdapter.insertTraveller(travel_id, person_id);

		}
		mPersonDbAdapter.close();
		mTravellersAdapter.close();


		return travel_id;
	}


	/**
	 * SQL = SELECT * FROM travel
	 * 
	 * @return cursor to result list
	 */
	public Cursor getTravels() {
		Log.i(TAG, "getTravelInfo");
		return mDb.query(TABLE_NAME, new String[] {TRAVEL_ID, TITLE, DESCRIPTION, CREATOR_ID}, null, null, null, null, null);
	}

	/**
	 * SQL = SELECT * FROM travel WHERE _id = travel_id
	 * 
	 * @return cursor to result list
	 */
	public Cursor getTravels(int travel_id) {
		Log.i(TAG, "getTravelInfo");
		String where = "_id="+travel_id;
		return mDb.query(TABLE_NAME, new String[] {TITLE, DESCRIPTION, CREATOR_ID}, where, null, null, null, null);
	}

	/**
	 * update travel together with travellers
	 * @param travel
	 * @param activity
	 * @return
	 */
	public boolean updateTravel(Travel travel, EditTravel activity) {

		//FIXME NOT OPTIMIZED !!!
		Log.i(TAG, "update Travel");

		PersonDbAdapter mTravelAdapter = new PersonDbAdapter(activity);
		TravellersDbAdapter mTravellersAdapter = new TravellersDbAdapter(activity);
		mTravellersAdapter.open();
		mTravelAdapter.open();

		//      Update travel 
		String where = TRAVEL_ID + "=" + travel.getTravelId();

		ContentValues values = new ContentValues();
		values.put(TITLE, travel.getTitle());
		values.put(DESCRIPTION, travel.getDescription());

		long travel_id = mDb.update(TABLE_NAME, values, where, null);

		//		remove deleted travelers		

		Person traveller;
		String db_id = "", id = "";

//		List<String> idsInDB= new ArrayList<String>();
		
		Cursor cur = mTravellersAdapter.getTravellers(travel.getTravelId());
//		while (cur.moveToNext()) {
//			
//		}	
		
		
//		Iterator<String> iDbTravellers = idsInDB.iterator();
		while(cur.moveToNext()){
			db_id = cur.getString(cur.getColumnIndexOrThrow(TravellersDbAdapter.PERSON_ID));
		
			boolean exist = false;
			
			Iterator<Person> iTravellers = travel.getTravellers().iterator();
			while (iTravellers.hasNext()){
				traveller = (Person)iTravellers.next();
				try {
					id = String.valueOf(PersonDbAdapter.getPersonId(traveller.getPhoneNumber()));
					Log.i(TAG, db_id + " == " +id);
					if( db_id.equals(id)){
						exist = true;
						break;
					}
				} catch (RuntimeException e) {
					// person does not exist so no need to do anything
				}
			}
			
			if (!exist){
				Log.i(TAG, " ==> " +db_id+ "> delete");
				TravellersDbAdapter.removePerson(travel.getTravelId()+"", db_id);
			}
		}


		//add new travelers		

		Iterator<Person> iTravellers = travel.getTravellers().iterator();
		while (iTravellers.hasNext()){
			traveller = (Person)iTravellers.next();
			long person_id = -1;

			if (PersonDbAdapter.personExists(traveller.getPhoneNumber())){
				person_id = PersonDbAdapter.getPersonId(traveller.getPhoneNumber());
			} else {
				person_id = PersonDbAdapter.insertPerson(traveller.getName(),traveller.getPhoneNumber());
			}

			if (!TravellersDbAdapter.relationExists(travel.getTravelId(), person_id)){
				Log.i(TAG, ">> relation does not exist");
				TravellersDbAdapter.insertTraveller(travel.getTravelId(), person_id);
			}else{
				Log.i(TAG, ">> relation exists");
			}
		}

		mTravelAdapter.close();
		mTravellersAdapter.close();

		return true;

	}


}
